<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

function update_rating($id, $rating, $old_rating) {
    if($rating == $old_rating) {
        return;
    }
    if(!id || $rating < -1 || $rating > 2)
        return FALSE;
    if ($old_rating == '2') {
        $query = "UPDATE interests SET likes = likes - 1 WHERE id = '$id'";
        safe_update_query($query);
    } else if ($old_rating == '0') {
        $query = "UPDATE interests SET dislikes = dislikes - 1 WHERE id = '$id'";
        safe_update_query($query);
    } else if ($old_rating == '-1') {
        $query = "UPDATE interests SET neutrals = neutrals - 1 WHERE id = '$id'";
        safe_update_query($query);
    }
    if ($rating == '2') {
        $query = "UPDATE interests SET likes = likes + 1 WHERE id = '$id'";
        $return = safe_update_query($query);
    } else if ($rating == '0') {
        $query = "UPDATE interests SET dislikes = dislikes + 1 WHERE id = '$id'";
        $return = safe_update_query($query);
    } else if ($rating == '-1') {
        $query = "UPDATE interests SET neutrals = neutrals + 1 WHERE id = '$id'";
        $return = safe_update_query($query);
    }
    
    return $return;
}

function get_interest($id) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE i.id = '$id'";
    
    $result = safe_select_query($query);
    return $result[0];
}

function get_interests($user_id, $limit, $offset) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                ORDER BY name ASC
                LIMIT $offset, $limit";
    
    return safe_select_query($query);
}

function get_interests_of($user_id, $limit, $offset) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE id IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id')
                ORDER BY name ASC
                LIMIT $offset, $limit";
    
    return safe_select_query($query);
}

function get_interest_by_name($name) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE i.name = '$name'";
    
    $result = safe_select_query($query);
    return $result[0];
}

function get_interest_by_name_and_category_id($name, $category_id) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE i.name = '$name' AND i.category_id = '$category_id'"; 
    
    $result = safe_select_query($query);
    return $result[0];
}

function get_interests_by_name($user_id, $names, $limit, $offset) {
    $names_query = "1";
    foreach($names as $name) {
        $names_query .= " AND name LIKE '%$name%'";
    }
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE $names_query
                AND id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                ORDER BY name ASC
                LIMIT $offset, $limit";
    
    return safe_select_query($query);
}

function get_interests_by_name_and_category_id($user_id, $names, $category_id, $limit, $offset) {
    $names_query = "1";
    foreach($names as $name) {
        $names_query .= " AND name LIKE '%$name%'";
    }
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE $names_query
                AND i.category_id = '$category_id'
                AND id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                ORDER BY name ASC
                LIMIT $offset, $limit";
    
    return safe_select_query($query);
}


function fulltext_get_interest_by_name($name) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE MATCH(i.name) AGAINST('$name')";
    
    $result = safe_select_query($query);
    return $result[0];
}

function fulltext_get_interest_by_name_and_category_id($name, $category_id) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE MATCH(i.name) AGAINST('$name') AND category_id = '$category_id'";
    
    $result = safe_select_query($query);
    return $result[0];
}

function fulltext_get_interests_by_name($user_id, $names, $limit, $offset) {
    $names_query = "MATCH(i.name) AGAINST ('1";
    foreach($names as $name) {
        $names_query .= " $name";
    }
    $names_query .= "')";
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE $names_query
                AND id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                
                LIMIT $offset, $limit";
    
    return safe_select_query($query);
}

function fulltext_get_interests_by_name_and_category_id($user_id, $names, $category_id, $limit, $offset) {
    $names_query = "MATCH(name) AGAINST ('1";
    foreach($names as $name) {
        $names_query .= " $name";
    }
    $names_query .= "')";
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE $names_query
                AND i.category_id = '$category_id'
                AND id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                
                LIMIT $offset, $limit";
    
    return safe_select_query($query);
}

function combo_get_interest_by_name($name) {
    if(strlen($name) <= 3) {
        return get_interest_by_name($name);
    } else {
        return fulltext_get_interest_by_name($name);
    }
}

function combo_get_interest_by_name_and_category_id($name, $category_id) {
    if(strlen($name) <= 3) {
        return get_interest_by_name_and_category_id($name, $category_id);
    } else {
        return fulltext_get_interest_by_name_and_category_id($name, $category_id);
    }
}

function combo_get_interests_by_name($user_id, $names, $limit, $offset) {
    $likes_query = "1";
    $likes_count = 0;
    foreach($names as $name) {
        if(strlen($name) <= 3) {
            $likes_count++;
            $likes_query .= " AND name LIKE '%$name%'";
        }
    }
    if(!$likes_count) {
        $likes_query = 0;
    }
    $names_query = "MATCH(i.name) AGAINST ('1";
    foreach($names as $name) {
        if(strlen($name) > 3)
            $names_query .= " $name";
    }
    $names_query .= "')";
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE (($names_query) OR ($likes_query))
                AND id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                
                LIMIT $offset, $limit";
    //echo "$query<br>";
    return safe_select_query($query);
}

function combo_get_interests_by_name_and_category_id($user_id, $names, $category_id, $limit, $offset) {
    $likes_query = "1";
    $likes_count = 0;
    foreach($names as $name) {
        if(strlen($name) <= 3) {
            $likes_count++;
            $likes_query .= " AND name LIKE '%$name%'";
        }
    }
    if(!$likes_count) {
        $likes_query = 0;
    }
    $names_query = "MATCH(name) AGAINST ('1";
    foreach($names as $name) {
        if(strlen($name) > 3)
            $names_query .= " $name";
    }
    $names_query .= "')";
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE (($names_query) OR ($likes_query))
                AND i.category_id = '$category_id'
                AND id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                
                LIMIT $offset, $limit"; 
    ///echo $query;
    return safe_select_query($query);
}

function add_interest($user_id, $category_id, $name, $description, $geographic_level, $source, $id_str) {
    if(!$user_id || !$category_id || strlen($name) <= 0)
        return FALSE;
    
    $name           = mysql_real_escape_string($name);
    $description    = mysql_real_escape_string($description);
    $id_str         = mysql_real_escape_string($id_str);
    
    $time = time();
    $query = "INSERT INTO interests (user_id, category_id, name, description, geographic_level, source, id_str, timestamp, created)
                            VALUES ('$user_id', '$category_id', '$name', '$description' , '$geographic_level', '$source', '$id_str', '$time', '$time')";
    //echo $query;
    return safe_insert_query($query);
}

function update_interest() {
    
}

function delete_interest($id) {
    $query = "DELETE FROM interests WHERE id = '$id'";
}

function get_popular_interests($user_id, $limit, $offset) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                ORDER BY likes DESC, dislikes ASC 
                LIMIT $offset, $limit";
    return safe_select_query($query);
}

function get_recent_interests($user_id, $limit, $offset) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                ORDER BY timestamp DESC, likes DESC, dislikes ASC 
                LIMIT $offset, $limit";
    return safe_select_query($query);
}

/*
function get_nearby_interests($user_id, $lat, $lng, $distance, $limit, $offset) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created,
                ( 6371 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) as distance
                FROM interests i, interest_locations l 
                WHERE i.id = l.object_id
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                HAVING distance <= $distance
                ORDER BY distance, i.source, i.id_str, i.timestamp DESC, i.likes DESC, i.dislikes ASC
                LIMIT $offset, $limit";
    
    return safe_select_query($query);
}
*/

function get_nearby_interests($user_id, $lat, $lng, $distance, $limit, $offset) {
    $epsilon = sqrt($distance * $distance / 7570);
    $min_lat = $lat - $epsilon;
    $min_lng = $lng - $epsilon;
    $max_lat = $lat + $epsilon;
    $max_lng = $lng + $epsilon;
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created,
                ( 6371 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) as distance
                FROM interests i, interest_locations l 
                WHERE i.id = l.object_id
                AND lat >= '$min_lat' AND lat <= '$max_lat' AND lng >= '$min_lng' AND lng <= '$max_lng'
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id') 
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                HAVING distance <= $distance
                ORDER BY distance, i.source, i.id_str, i.timestamp DESC, i.likes DESC, i.dislikes ASC
                LIMIT $offset, $limit";
    //echo $query;
    return safe_select_query($query);
}

function suggest_intererests_trend($user_id, $limit, $offset) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                ORDER BY likes DESC, dislikes ASC 
                LIMIT $offset, $limit";
    return safe_select_query($query);
}

function suggest_intererests_friend($user_id, $limit, $offset) {
    $query = "SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                FROM interests i
                WHERE id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id')
                AND id IN ((SELECT DISTINCT object_id FROM interest_ratings WHERE user_id IN (SELECT friend_id opinions_friends WHERE user_id = '$user_id')))
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                ORDER BY id DESC
                LIMIT $offset, $limit";
    return safe_select_query($query);
}

function suggest_intererests_combo($user_id, $limit, $offset) {
    $query = "SELECT DISTINCT *
                FROM (
                        ((SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                        FROM interests i
                        WHERE id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id')
                        AND id IN ((SELECT DISTINCT object_id FROM interest_ratings WHERE user_id IN (SELECT friend_id FROM opinions_friends WHERE user_id = '$user_id')))
                        AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                        AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                        ORDER BY likes DESC, dislikes ASC LIMIT $offset, $limit)
                UNION
                        (SELECT i.id, i.user_id, i.category_id, i.name, i.description, i.geographic_level, i.likes, i.dislikes, i.neutrals, i.source, i.id_str, i.timestamp, i.created
                        FROM interests i
                        WHERE id NOT IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id')
                        AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_reports WHERE user_id = '$user_id')
                        AND i.id NOT IN (SELECT DISTINCT object_id FROM interest_abuses)
                        ORDER BY likes DESC, dislikes ASC LIMIT $offset, $limit)) temp
                )
                ORDER BY id DESC, likes DESC, dislikes ASC LIMIT $offset, $limit
                ";
    return safe_select_query($query);
}

function remove_rated_gowalla_places($user_id, $lat, $lng, $distance, $ids) {
    //print_r($ids);
    $id_list = "(0";
    foreach($ids as $id) {
        $id_list .= ", '$id'";
    }
    $id_list .= ")";
    $epsilon = sqrt($distance * $distance / 7570);
    $min_lat = $lat - $epsilon;
    $min_lng = $lng - $epsilon;
    $max_lat = $lat + $epsilon;
    $max_lng = $lng + $epsilon;
    //AND i.id IN (SELECT DISTINCT object_id FROM interest_ratings WHERE user_id = '$user_id')
    $query = "SELECT i.id_str
                FROM interests i, interest_locations l 
                WHERE i.id = l.object_id
                AND lat >= '$min_lat' AND lat <= '$max_lat' AND lng >= '$min_lng' AND lng <= '$max_lng'
                
                AND i.id_str IN $id_list";
    //echo $query;
    $results = safe_select_query($query);
    
    $unrated = array();
    
    foreach($ids as $id) {
        $dup = FALSE;
        foreach($results as $result) {
            if($id == $result->id_str) {
                $dup = TRUE;
                break;
            } else {
                
            }
        }
        if($dup) {
            
        } else {
            $unrated[] = $id;
        }
    }
    //print_r($unrated);
    return $unrated;
}

?>
